package db_servicer

import (
	"fmt"
	//	"regexp"
	"gopkg.in/rana/ora.v3"
)

/*
var ses *ora.Ses
var logger *log.Logger

*/
// 应该将报错信息 的生成，集中在一处，最佳位置是数据库存贮过程
// 故，建议返回值结构增加一项：　ｆａｌｇ　ｉｎｔ８，　ｍｓｇ　ｓｔｒｉｎｇ，　ｄａｔａ　　ｓｔｒｉｎｇ

func Device_List_per_Pond(p_user_id, p_pond_id int) []string {

	procRset := &ora.Rset{}

	stmtRefCall, err := ses.Prep("CALL pkg_device_data.Device_List_per_Pond(:p1,:p2, :p3)")

	defer stmtRefCall.Close()
	if err != nil {
		logger.Panicf("Panic in Device_list_per_Pond, Prep err:%v\n", err)
	}

	_, err = stmtRefCall.Exe(procRset, int32(p_user_id), int32(p_pond_id))
	if err != nil {
		logger.Panicf("Panic in Device_list_per_Pond, Exe err:%v\n", err)
	}

	var devices []string

	if procRset.IsOpen() {
		//        logger.Println( "\n ............. procRset Is open ....... ")
		for procRset.Next() {
			devices = append(devices, fmt.Sprintf("%v", procRset.Row[0]))
		}
	}
	return devices

}

func PondInfo_HistDaily(p_user_id, p_pond_id int, from_date, to_date, agg_level string) dev_retMsg {

	// Due to huge rows of his data of each devie, 1 row every 5 minutes, 12*24=288 rows
	// we need handle the hist data row by row, and put them into JSON in golang
	//   first get all device_id
	//   then for each device_id, fetch hist data row by row

	var v_flag int8
	var v_ret_msg string

	//	logger = log.New(os.Stdout, ">>>>>>>DB.PondInfo Hist Daily Summary  :", log.LstdFlags|log.Lshortfile)

	procRset := &ora.Rset{}

	stmtRefCall, err := ses.Prep(fmt.Sprintf("CALL pkg_device_data.Dev_Data_Hist%v(:p1,:p2 , :p3, :p4, :p5, :p6 ,:p7)", agg_level))

	defer stmtRefCall.Close()
	if err != nil {
		logger.Panicf("***Panic in PondInfo_HistDaily(ses.Prep): %v ***\n", err)
	}

	_, err = stmtRefCall.Exe(&v_flag, &v_ret_msg, procRset, int32(p_user_id), int32(p_pond_id), from_date, to_date)
	if err != nil {
		logger.Panicf("***Panic in PondInfo_HistDaily(stmtRefCall.exe): %v ***\n", err)
	}

	// logger.Printf(">>> rowsAffected:%v, flag: %v, Msg: %v, procRset[%T]: %v",rowsAffected, v_flag,  v_ret_msg, procRset, procRset)
	//

	if v_flag != 0 {
		return dev_retMsg{
			Flag:    v_flag,
			Message: v_ret_msg,
			Data:    fmt.Sprintf(`{"flag":%v,"message":"%v","data":[ ]}`, v_flag, v_ret_msg)}
	}
	var v_data string

	var device_id string

	var prefix string
	var data_item string

	v_dev_ct := 0

	if procRset.IsOpen() {
		//        logger.Println( "\n ............. procRset Is open ....... ")
		v_pre_dev_id := ""

		for procRset.Next() {

			device_id = fmt.Sprintf("%v", procRset.Row[0])

			data_item = fmt.Sprintf(
				`{
"as_of_date":"%v",
"max_surface_temp":%v,"avg_surface_temp":%v,"min_surface_temp":%v,
"max_deep_temp":%v,"avg_deep_temp":%v,"min_deep_temp":%v,
"max_surface_oxy":%v,"avg_surface_oxy":%v,"min_surface_oxy":%v,
"max_deep_oxy":%v,"avg_deep_oxy":%v,"min_deep_oxy":%v,
"max_ph_value":%v ,"avg_ph_value":%v ,"min_ph_value":%v ,
"ds_count":%v }`,
				procRset.Row[3], procRset.Row[4], procRset.Row[5], procRset.Row[6], procRset.Row[7], procRset.Row[8],
				procRset.Row[9], procRset.Row[10], procRset.Row[11], procRset.Row[12], procRset.Row[13],
				procRset.Row[14], procRset.Row[15], procRset.Row[16],
				procRset.Row[17], procRset.Row[18], procRset.Row[19])

			if device_id != v_pre_dev_id {
				v_dev_ct += 1
				v_pre_dev_id = device_id

				prefix = fmt.Sprintf(`{"device_id":"%v","device_type":"%v","pond_name":"%v","data_hist":[`,
					device_id, procRset.Row[1], procRset.Row[2])

				if v_data != "" {
					prefix = "]}," + prefix
				}
			} else {
				prefix = ""
				data_item = "," + data_item
			}

			v_data = v_data + prefix + data_item

		} //End of procRest.Next

		if v_data != "" {
			v_data = v_data + "]}"
		}
	} // End of  procRset.IsOpen()

	if procRset.Err != nil {
		logger.Panicf("***Panic in PondInfo_HistDaily, ProcRset.Err: %v ***\n", procRset.Err)

	}

	return dev_retMsg{
		Flag:    v_flag,
		Message: v_ret_msg,
		Data:    fmt.Sprintf(`{"flag":%v,"message":"%v","device_ct":%v,"data":[%v]}`, v_flag, v_ret_msg, v_dev_ct, v_data)}

}

func PondInfo_HistList(p_user_id, p_pond_id int, qry_date string) dev_retMsg {

	// Due to huge rows of his data of each devie, 1 row every 5 minutes, 12*24=288 rows
	// we need handle the hist data row by row, and put them into JSON in golang
	//   first get all device_id
	//   then for each device_id, fetch hist data row by row

	var v_flag int8
	var v_ret_msg string

	// logger = log.New(os.Stdout, ">>>>>>>DB.PondInfo Hist List :", log.LstdFlags|log.Lshortfile)

	procRset := &ora.Rset{}

	stmtRefCall, err := ses.Prep("CALL pkg_device_data.Dev_Data_HistList(:p1,:p2 , :p3, :p4, :p5, :p6 )")

	defer stmtRefCall.Close()
	if err != nil {
		logger.Panicf("***Panic in PondInfo_HistList(ses.Prep): %v ***\n", err)
	}

	_, err = stmtRefCall.Exe(&v_flag, &v_ret_msg, procRset, int32(p_user_id), int32(p_pond_id), qry_date)
	if err != nil {
		logger.Panicf("***Panic in PondInfo_HistList(stmtRefCall.exe): %v ***\n", err)
	}

	// logger.Printf(">>> rowsAffected:%v, flag: %v, Msg: %v, procRset[%T]: %v",rowsAffected, v_flag,  v_ret_msg, procRset, procRset)
	//

	if v_flag != 0 {
		return dev_retMsg{
			Flag:    v_flag,
			Message: v_ret_msg,
			Data:    fmt.Sprintf(`{"flag":%v,"message":"%v","devices":[ ]}`, v_flag, v_ret_msg)}
	}
	var v_data string

	var device_id string

	var prefix string
	var data_item string

	v_dev_ct := 0

	if procRset.IsOpen() {
		//        logger.Println( "\n ............. procRset Is open ....... ")
		v_pre_dev_id := ""

		for procRset.Next() {

			device_id = fmt.Sprintf("%v", procRset.Row[0])

			// 0	d.device_id
			// 1	l.device_type
			// 2	p.pond_name
			// 3	updated_time
			// 4	surface_temp
			// 5	deep_temp
			// 6	surface_oxy
			// 7	deep_oxy
			// 8	ph_value
			// 9	oxy_pole_status
			// 10	surface_water_pump_status
			// 11	deep_water_pump_status
			// 12	ph_sensor_status
			// 13	ammonia_nitrogen_status
			// 14	other_status
			// 15	temp_status
			// 16	power_supply_status

			data_item = fmt.Sprintf(
				`{"updated_time":"%v","surface_temp":%v,"deep_temp":%v,"surface_oxy":%v,"deep_oxy":%v,
				"ph_value":%v,"oxy_pole_status":%v,"surface_water_pump_status":%v,
				"deep_water_pump_status":%v,"ph_sensor_status":%v,"ammonia_nitrogen_status":%v,
				"other_status":%v,"temp_status":%v,"power_supply_status":%v}`,
				procRset.Row[3], procRset.Row[4], procRset.Row[5], procRset.Row[6], procRset.Row[7], procRset.Row[8],
				procRset.Row[9], procRset.Row[10], procRset.Row[11], procRset.Row[12],
				procRset.Row[13], procRset.Row[14], procRset.Row[15], procRset.Row[16])

			if device_id != v_pre_dev_id {
				v_dev_ct += 1
				v_pre_dev_id = device_id

				prefix = fmt.Sprintf(`{"device_id":"%v","device_type":"%v","pond_name":"%v","data_hist":[`,
					device_id, procRset.Row[1], procRset.Row[2])

				if v_data != "" {
					prefix = "]}," + prefix
				}
			} else {
				prefix = ""
				data_item = "," + data_item
			}

			v_data = v_data + prefix + data_item

		}
		if v_data != "" {
			v_data = v_data + "]}"
		}
	} // End of  procRset.IsOpen()

	if procRset.Err != nil {
		logger.Panicf("***Panic in PondInfo_HistList(procRest.Err): %v ***\n", err)
	}

	return dev_retMsg{
		Flag:    v_flag,
		Message: v_ret_msg,
		Data:    fmt.Sprintf(`{"flag":%v,"message":"%v","device_ct":%v,"devices":[%v]}`, v_flag, v_ret_msg, v_dev_ct, v_data)}

}

func Realtime_PondInfo(p_user_id int) dev_retMsg {

	// logger = log.New(os.Stdout, ">>>>>>>DB.Realtime PondInfo:", log.LstdFlags|log.Lshortfile)

	//	procRset := &ora.Rset{}

	stmtRefCall, err := ses.Prep("select * from table( pkg_device_data.Realtime_data(:p1 ))")
	defer stmtRefCall.Close()

	if err != nil {
		logger.Printf("Panic in ses.Prep, Error:%v\n", err)
		return dev_retMsg{
			Flag:    -1,
			Message: err.Error(),
			Data:    fmt.Sprintf(`{"flag":%v,"message":"%v","data":[]}`, -1, "读取塘口实时数据失败"),
		}
	}

	procRset, err := stmtRefCall.Qry(int32(p_user_id))
	if err != nil {
		logger.Printf("***Panic in Realtime_PondInfo,   err: %v ***\n", err)
		return dev_retMsg{
			Flag:    -1,
			Message: err.Error(),
			Data:    fmt.Sprintf(`{"flag":%v,"message":"%v","data":[]}`, -1, "读取塘口实时数据失败"),
		}

	}

	// logger.Printf(">>> rowsAffected:%v, flag: %v, Msg: %v, procRset[%T]: %v",rowsAffected, v_flag,  v_ret_msg, procRset, procRset)
	//

	d := make([]byte, 80000)
	i := 0
	for procRset.Next() {
		i += copy(d[i:], fmt.Sprintf(",%v ", procRset.Row[0]))
	}

	var v_flag int8
	var v_ret_msg string
	var v_data string

	if i > 1 {
		v_flag = 0
		v_ret_msg = "成功读取塘口最新数据"
		v_data = string(d[1:i])

	} else {
		v_flag = 1
		v_ret_msg = "没有塘口实时数据"
		v_data = ""

	}

	return dev_retMsg{
		Flag:    v_flag,
		Message: v_ret_msg,
		Data:    fmt.Sprintf(`{"flag":%v,"message":"%v","devices":[%v]}`, v_flag, v_ret_msg, v_data)}

}

/*  try to figure out oracle : too many curosrs open ....
func Realtime_PondInfo(p_user_id int) dev_retMsg {

	var v_flag int8
	var v_ret_msg string

	// logger = log.New(os.Stdout, ">>>>>>>DB.Realtime PondInfo:", log.LstdFlags|log.Lshortfile)

	procRset := &ora.Rset{}

	stmtRefCall, err := ses.Prep("CALL pkg_device_data.Realtime_PondInfo(:p1,:p2 , :p3, :p4)")

	defer stmtRefCall.Close()
	if err != nil {
		logPanic(fmt.Sprintf("Panic, retMsg:%v, Error:%v\n", v_ret_msg, err))
	}

	_, err = stmtRefCall.Exe(&v_flag, &v_ret_msg, procRset, int32(p_user_id))
	if err != nil {
		logPanic(fmt.Sprintf("***Panic in Realtime_PondInfo, retMsg:%v, err: %v ***\n", v_ret_msg, err))
	}

	// logger.Printf(">>> rowsAffected:%v, flag: %v, Msg: %v, procRset[%T]: %v",rowsAffected, v_flag,  v_ret_msg, procRset, procRset)
	//
	return Refcursor_to_string(v_flag, v_ret_msg, &procRset)
}
*/

func Fetch_Device_Align(p_user_id int, p_device_id string) dev_retMsg {

	var v_flag int8
	var v_ret_msg string
	var v_data_msg string

	// logger = log.New(os.Stdout, ">>>>>>>DB.Fetch Device Align Information:", log.LstdFlags|log.Lshortfile)

	if _, err := ses.PrepAndExe("CALL pkg_device_data.fetch_Device_Align(:p1,:p2 , :p3, :p4, :p5  )",
		&v_flag, &v_ret_msg, &v_data_msg, int32(p_user_id), p_device_id); err != nil {

		logPanic(fmt.Sprintf("***Panic in Fetch_Device_Align, retMsg:%v, err: %v ****\n", v_ret_msg, err))
	}

	// logger.Println(">>>user_id:", p_user_id , ",flag:", v_flag,  ",Msg:", v_ret_msg, "Data:", v_data_msg)

	return dev_retMsg{Flag: v_flag,
		Message: v_ret_msg,
		Data:    v_data_msg,
	}

}

func Cmd_Device_Align(p_user_id int, p_device_id string) dev_retMsg {

	var v_flag int8
	var v_ret_msg string
	var v_data_msg string
	var v_created_by string = "Android App"

	// logger = log.New(os.Stdout, ">>>>>>>DB.Command Device to Align:", log.LstdFlags|log.Lshortfile)

	if _, err := ses.PrepAndExe("CALL pkg_device_data.Cmd_Device_Align(:p1,:p2 , :p3, :p4, :p5, :p6  )",
		&v_flag, &v_ret_msg, &v_data_msg, int32(p_user_id), p_device_id, v_created_by); err != nil {

		logPanic(fmt.Sprintf("***Panic in Cmd_Device_Align, retMsg:%v, err: %v ***\n", v_ret_msg, err))
	}

	// logger.Println(">>>user_id:", p_user_id , ",flag:", v_flag,  ",Msg:", v_ret_msg, "Data:", v_data_msg)

	return dev_retMsg{Flag: v_flag,
		Message: v_ret_msg,
		Data:    v_data_msg,
	}

}

func Get_Device_Margin(p_user_id int, p_device_id string) dev_retMsg {

	var v_flag int8
	var v_ret_msg string
	var v_data_msg string

	// logger = log.New(os.Stdout, ">>>>>>>DB.Get Device Margin:", log.LstdFlags|log.Lshortfile)

	if _, err := ses.PrepAndExe("CALL pkg_device_data.get_Device_Margin(:p1,:p2 , :p3, :p4, :p5  )",
		&v_flag, &v_ret_msg, &v_data_msg, int32(p_user_id), p_device_id); err != nil {

		logPanic(fmt.Sprintf("***Panic in Get_Device_Margin, retMsg:%v, %v ****\n", v_ret_msg, err))
	}

	// logger.Println(">>>user_id:", p_user_id , ",flag:", v_flag,  ",Msg:", v_ret_msg, "Data:", v_data_msg)

	return dev_retMsg{Flag: v_flag,
		Message: v_ret_msg,
		Data:    v_data_msg,
	}

}

func Set_Device_Margin(p_user_id int, p_device_id string, p_oxy_min, p_ph_min, p_ph_max, p_ammonia_nitrogen, p_temperature_min float64) dev_retMsg {

	var v_flag int8
	var v_ret_msg string
	var v_data_msg string
	var v_created_by string = "Android App"

	// logger = log.New(os.Stdout, ">>>>>>>DB.Set Device Margin:", log.LstdFlags|log.Lshortfile)

	if _, err := ses.PrepAndExe("CALL pkg_device_data.set_Device_Margin(:p1,:p2 , :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11  )",
		&v_flag, &v_ret_msg, &v_data_msg, int32(p_user_id), p_device_id, v_created_by,
		p_oxy_min, p_ph_min, p_ph_max, p_ammonia_nitrogen, p_temperature_min); err != nil {

		logPanic(fmt.Sprintf("***Panic in Set_Device_Margin, retMsg:%v, err:%v ***\n", v_ret_msg, err))
	}

	// logger.Println(">>>user_id:", p_user_id , ",flag:", v_flag,  ",Msg:", v_ret_msg, "Data:", v_data_msg)

	return dev_retMsg{Flag: v_flag,
		Message: v_ret_msg,
		Data:    v_data_msg,
	}

}

/*
func Send_To_Device( p_device_id string ) (flag int8, ip_addr  string , out_msg string) {
// get list of down stream data from table dev_outgoing_log
	// send to devcie , use the cu ip and port from device_list
	// update the status  of  dev_outgoing_log
	logger = log.New(os.Stdout, ">>>>>>>DB.Send To Device :", log.LstdFlags|log.Lshortfile)


	var v_ret string

	if _, err := ses.PrepAndExe("CALL pkg_tcp_svr.dev_outgoing_lists(:p1,:p2)",
			p_device_id,  &v_ret); err != nil {

//		logger.Fatal(err)
		logger.Print(err)
		return   -1 , fmt.Sprint(err) , ""  // v_ret
	}

	if regexp.MustCompile(`^ORA-[0-9]{1,5}:`).MatchString(v_ret) {
		logger.Print(v_ret)
		return -1 , v_ret , ""
	}

	logger.Println(" outgoing msg: ", v_ret )

	if len(v_ret)  < 17 { // device_id fix 15 bytes length
		return 1, " Nothing to do " , ""
	}


	var v_flag int8;
	var v_ret_msg  string;
	var v_ip_addr string ;
	var v_device_sn int32;


	// PROCEDURE get_device_ip (
 //        p_flag          OUT   PLS_INTEGER
	//    ,p_ret_msg       OUT   VARCHAR2
 //       ,p_device_id        IN VARCHAR2
	//    ,p_device_sn      OUT  PLS_INTEGER
	//    ,p_ip_addr        OUT  VARCHAR2
	// )
		if _, err := ses.PrepAndExe("CALL pkg_device_info.get_device_ip(:p1,:p2, :p3, :p4, :p5)",
			&v_flag, &v_ret_msg, p_device_id, &v_device_sn,   &v_ip_addr ); err != nil {

//		logger.Fatal(err)
		logger.Print(err)
		return   -1 , v_ret_msg  , ""
	}

	logger.Println("flag:", v_flag, ", msg:", v_ret_msg, ", ip:", v_ip_addr )

	return 0, v_ip_addr, v_ret
/////////// send out data ///////////////////////////////////
//		conn.SetReadDeadline(time.Now().Add(60 * 3.1 * time.Second)) // set 3 minutes timeout

}

*/
